<?php
class M_prestasi extends Nvl_Model{
	function __construct(){
		parent::__construct();
		$this->table_name='t_prestasi';
		$this->column_primary='prestasi_id';
		$this->column_order='';
		$this->column_unique='';
		$this->column_active='';
	}
	
	function get_list($start, $end, $search_text){
		$this->db->select("
			t_prestasi.*,
			t_lomba.*,
			m_juara.*,
			prestasi_sekolah_id,
			prestasi_sekolah_nama
		");
		if($_SESSION[SESSION_USERGROUPID] == 4 OR $_SESSION[SESSION_USERGROUPID] == 5){
			$this->db->where('prestasi_sekolah_id', $_SESSION[SESSION_SCHOOLID]);
		}
		if($_SESSION[SESSION_USERGROUPID] == 5){
			$this->db->where('prestasi_create_by', $_SESSION[SESSION_USERID]);
		}
		
		if($search_text != ''){
			$this->db->or_like('prestasi_sekolah_nama', $search_text);
			$this->db->or_like('juara_nama', $search_text);
			$this->db->or_like('lomba_judul', $search_text);
		}
		if($start != 0 && $end != 0){
			$this->db->limit($end, $start);
		}
		$this->db->join('t_lomba', 'lomba_id = prestasi_lomba_id');
		$this->db->join('m_juara', 'juara_id = prestasi_juara_id');
		$record = $this->db->get('t_prestasi');
		$final[] = array(
			"total"=>$record->num_rows(),
			"results"=>$record->result()
		);
		return json_encode($final);
	}
	
	function get_list_siswa($start, $end, $search_text, $DB){
		$num_rows = 0;
		$results = NULL;
		
		if($search_text != ''){
			$DB->where('npsn', $_SESSION[SESSION_SCHOOLID]);
			if($search_text != ''){
				$DB->or_like('lower(nama)', strtolower($search_text));
				$DB->or_like('lower(nik)', strtolower($search_text));
				$DB->or_like('lower(nisn)', strtolower($search_text));
			}
			if($start != 0 && $end != 0){
				$DB->limit($end, $start);
			}
			$DB->select("nama || ' - ' || nis AS siswa_ket, nama AS siswa_nama, nisn AS siswa_nis, alamat AS siswa_alamat, kelas AS siswa_kelas, tgl_lahir as siswa_tgllahir", FALSE);
			$record = $DB->get('siswa');
			$num_rows = $record->num_rows();
			$results = $record->result();
		}
		$final[] = array(
			"total"=>$num_rows,
			"results"=>$results
		);
		return json_encode($final);
	}
	
	function get_list_detail($prestasi_id){
		$this->db->where('det_prestasi_prestasi_id', $prestasi_id);
		$record = $this->db->get('t_prestasi_det');
		$final[] = array(
			"total"=>$record->num_rows(),
			"results"=>$record->result()
		);
		return json_encode($final);
	}
	
	function get_list_juara_poin($lomba_id, $search_text, $juara_id = 0){
		$sql = "
			SELECT juara_id, juara_nama, skor_angka, skor_id, CONCAT(juara_nama ,' - [ ', skor_angka, ' poin ]') AS juara_ket FROM t_skor
			JOIN m_juara ON skor_juara_id = juara_id
			WHERE skor_level_id = (
				SELECT lomba_level_id FROM t_lomba WHERE lomba_id = ". addslashes($lomba_id) ."
			)
		";
		if($juara_id != 0){
			$sql .= " AND juara_id = ".$juara_id." LIMIT 1 ";
			$record = $this->db->query($sql);
			return $record->row()->skor_angka;
		}
		$record = $this->db->query($sql);
		$final[] = array(
			"total"=>$record->num_rows(),
			"results"=>$record->result()
		);
		return json_encode($final);
	}
	
}